﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections;

namespace ValidateAFE400Active
{
    class Program
    {
        static void Main(string[] args)
        {
            string distinctSql = "SELECT DISTINCT [AFE400RecordID], [AFETaskName] FROM [DL_AFETest400] ORDER BY [AFE400RecordID]";

            SqlConnection conn = getConn();
            try
            {
                conn.Open();

                SqlCommand commandDistinctSql = new SqlCommand(distinctSql, conn);
                SqlDataReader reader = commandDistinctSql.ExecuteReader();
                ArrayList recordIDList = new ArrayList();
                ArrayList afeTaskNameList = new ArrayList();
                while (reader.Read())
                {
                    recordIDList.Add(reader["AFE400RecordID"].ToString());
                    afeTaskNameList.Add(reader["AFETaskName"].ToString());

                    CheckData(reader["AFE400RecordID"].ToString(), reader["AFETaskName"].ToString());
                }

                conn.Close();
                Console.Read();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }

        static void CheckData(string afeRecordID, string afeTaskName)
        {
            string getGroupDataSql = "SELECT * FROM [DL_AFETest400] WHERE [AFE400RecordID] = ' + " + afeRecordID + "' AND [AFETaskName] = '" + afeTaskName + "'";
            SqlConnection conn = getConn();
            SqlDataReader reader = null;
            try
            {
                conn.Open();
                SqlCommand getGroupDataCommand = new SqlCommand(getGroupDataSql, conn);
                reader = getGroupDataCommand.ExecuteReader();
                int activeCount = 0;
                int errorID = 0;
                string latestTime = "";
                bool isSuccess = true;
                var timeList = new ArrayList();


                while (reader.Read())
                {
                    timeList.Add(reader["UTCCreationTime"].ToString());
                    if(reader["Active"].ToString().ToLower() == "true")
                    {
                        latestTime = reader["UTCCreationTime"].ToString();
                        activeCount++;
                        if(activeCount > 1)
                        {
                            errorID = reader.GetInt32(0);
                            break;
                        }
                    }
                }

                conn.Close();

                // activeCount must be 1, or it's not correct
                if(activeCount > 1 || activeCount == 0)
                {
                    Console.WriteLine("active column count is:" + activeCount.ToString());
                    Console.WriteLine("error data where AfeRecordID and AfeTaskName are : " + afeRecordID + "," + afeTaskName);
                    isSuccess = false;
                }

                // where the active colum is 1, the time must be the latest.
                if (isSuccess)
                {
                    for (int i = 0; i < timeList.Count; i++)
                    {
                        if (DateTime.Parse(latestTime) < DateTime.Parse(timeList[i].ToString()))
                        {
                            Console.WriteLine("latest active time is:" + latestTime);
                            Console.WriteLine("error data where AfeRecordID and AfeTaskName are : " + afeRecordID + "," + afeTaskName);
                            isSuccess = false;
                            break;
                        }
                    }
                }


                if (isSuccess)
                {
                    Console.WriteLine("Check Pass :" + afeRecordID + "," + afeTaskName);
                }
            }
            catch(Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            
        }

        static SqlConnection getConn()
        {
            string connstring = ConfigurationManager.ConnectionStrings["Default"].ConnectionString.ToString();

            return new SqlConnection(connstring);
        }


    }
}
